WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000;
SET ARRAYSIZE 1;

DECLARE
	/*
		Description:	Upload employee information
		Name:			Crystal
		Date:			2006/2/2		
	*/
   l_person_type_id        				NUMBER(15)     := NULL;	
   
   l_new_person_id                      crst_fnd_per_all_people.person_id%type;
   l_new_assignment_id                  crst_fnd_assignment.assignment_id%type;
   l_new_per_obj_ver_num      			crst_fnd_per_all_people.object_version_number%type; 
   l_new_asg_obj_ver_num  			    crst_fnd_per_all_people.object_version_number%type;
   l_new_per_effective_start_date       crst_fnd_per_all_people.effective_start_date%type;
   l_new_per_effective_end_date         crst_fnd_per_all_people.effective_end_date%type;
   l_new_full_name                      crst_fnd_per_all_people.full_name%type;   
   l_new_per_comment_id                 crst_fnd_per_all_people.comment_id%type; 
   l_new_assignment_sequence            crst_fnd_assignment.assignment_sequence%type;
   l_new_assignment_number				crst_fnd_assignment.assignment_number%type;
   l_new_name_combination_warning       BOOLEAN;
   l_new_assign_payroll_warning			BOOLEAN;
   l_new_orig_hire_warning           	BOOLEAN;
   l_bg_id								crst_fnd_per_all_people.business_group_id%type;
   l_start_date							per_all_people_f.start_date%type;
   l_effective_start_date				per_all_people_f.effective_start_date%type;
   
   func_error							EXCEPTION;
   
   l_per_information6					per_all_people_f.per_information6%type;
   l_national_identifier      			per_all_people_f.national_identifier%type;
   
   CURSOR cur_fnd_people IS
   		SELECT ppl.*
   		FROM crst_fnd_per_all_people ppl
   		--WHERE person_id=340
   		--WHERE rownum=1
   		ORDER BY person_id;		   		
BEGIN
   
   FOR rec IN cur_fnd_people LOOP
   		/*BEGIN
   			SELECT 	national_identifier,
   					per_information6,
   					start_date,
   					effective_start_date
   			INTO	l_national_identifier,
   					l_per_information6,
   					l_start_date,
   					l_effective_start_date
   			FROM	per_all_people_f
   			WHERE	person_id = rec.person_id
   			AND EFFECTIVE_START_DATE = rec.effective_start_date
   			and EFFECTIVE_END_DATE = rec.EFFECTIVE_END_DATE ;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				l_national_identifier:=NULL;
				l_per_information6:=NULL;  
				l_start_date:=NULL;
   				l_effective_start_date:=NULL;
			WHEN OTHERS THEN
				l_national_identifier:=NULL;
				l_per_information6:=NULL;  
				l_start_date:=NULL;
   				l_effective_start_date:=NULL;				
   		END;
   		*/   		
   		l_bg_id:=capbrimp_common.getBGID(rec.bg_name);
   		IF l_bg_id IS NULL THEN
   			raise func_error;
		END IF;   			
   		
   		BEGIN
			SELECT person_type_id
			INTO l_person_type_id
			FROM PER_PERSON_TYPES
			WHERE system_person_type = 'EMP'
			AND business_group_id = l_bg_id;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				l_person_type_id:=NULL;
			WHEN OTHERS THEN
				l_person_type_id:=NULL;				
		END;
   		   		
		/*hr_employee_api.create_employee
  				    (p_validate                      => false
  					,p_hire_date                     => rec.effective_start_date
  		--			,p_business_group_id           => rec.business_group_id
  					,p_business_group_id			 => l_bg_id
  					,p_last_name                     => rec.last_name 
  					,p_sex                           => rec.sex
  					,p_person_type_id                => rec.person_type_id
					,p_per_comments                  =>	null
  					,p_date_employee_data_verified   => rec.date_employee_data_verified
  					,p_date_of_birth                 => rec.date_of_birth
  					,p_email_address                 => rec.email_address
  					,p_employee_number               => rec.employee_number
  					,p_expense_check_send_to_addres  => rec.expense_check_send_to_address
  					,p_first_name                    => rec.first_name
  					,p_known_as                      => rec.known_as
  					,p_marital_status                => rec.marital_status
  					,p_middle_names                  => rec.middle_names
  					,p_nationality                   => rec.nationality
  					--,p_national_identifier           => rec.national_identifier
  					,p_national_identifier           => 'A123456(3)'
  					,p_previous_last_name            => rec.previous_last_name
  					,p_registered_disabled_flag      => rec.registered_disabled_flag
  					,p_title                         => rec.title
  					,p_vendor_id                     => rec.vendor_id
  					,p_work_telephone                => rec.work_telephone
  					,p_attribute_category            => rec.attribute_category
  					,p_attribute1 					 => rec.attribute1
		  			,p_attribute2 					 => rec.attribute2
		  			,p_attribute3 					 => rec.attribute3
		  			,p_attribute4 					 => rec.attribute4
		  			,p_attribute5 					 => rec.attribute5
		  			,p_attribute6 					 => rec.attribute6
		  			,p_attribute7 					 => rec.attribute7
		  			,p_attribute8 					 => rec.attribute8
		  			,p_attribute9 					 => rec.attribute9
		  			,p_attribute10 					 => rec.attribute10
		  			,p_attribute11 					 => rec.attribute11
		  			,p_attribute12 					 => rec.attribute12
		  			,p_attribute13 					 => rec.attribute13
		  			,p_attribute14 					 => rec.attribute14
		  			,p_attribute15 					 => rec.attribute15
		  			,p_attribute16 					 => rec.attribute16
		  			,p_attribute17 					 => rec.attribute17
		  			,p_attribute18 					 => rec.attribute18
		  			,p_attribute19 					 => rec.attribute19
		  			,p_attribute20 					 => rec.attribute20
		  			,p_attribute21 					 => rec.attribute21
		  			,p_attribute22 					 => rec.attribute21
		  			,p_attribute23 					 => rec.attribute23
		  			,p_attribute24 					 => rec.attribute24
		  			,p_attribute25 					 => rec.attribute25
		  			,p_attribute26 					 => rec.attribute26
		  			,p_attribute27 					 => rec.attribute27
		  			,p_attribute28 					 => rec.attribute28
		  			,p_attribute29 					 => rec.attribute29
		  			,p_attribute30 					 => rec.attribute30
		  			,p_per_information_category      => rec.per_information_category
  					,p_per_information1              => rec.per_information1
  					,p_per_information2              => rec.per_information2
  					,p_per_information3              => rec.per_information3
  					,p_per_information4              => rec.per_information4
  					,p_per_information5              => rec.per_information5
  					--,p_per_information6              => rec.per_information6
  					,p_per_information6              => rec.full_name
  					,p_per_information7              => rec.per_information7
  					,p_per_information8              => rec.per_information8
  					,p_per_information9              => rec.per_information9
  					,p_per_information10             => rec.per_information10
  					,p_per_information11             => rec.per_information11
  					,p_per_information12             => rec.per_information12
  					,p_per_information13             => rec.per_information13
  					,p_per_information14             => rec.per_information14
  					,p_per_information15             => rec.per_information15
  					,p_per_information16             => rec.per_information16
  					,p_per_information17             => rec.per_information17
  					,p_per_information18             => rec.per_information18
  					,p_per_information19             => rec.per_information19
  					,p_per_information20             => rec.per_information20
  					,p_per_information21             => rec.per_information21
  					,p_per_information22             => rec.per_information22
  					,p_per_information23             => rec.per_information23
  					,p_per_information24             => rec.per_information24
  					,p_per_information25             => rec.per_information25
  					,p_per_information26             => rec.per_information26
  					,p_per_information27             => rec.per_information27
  					,p_per_information28             => rec.per_information28
  					,p_per_information29             => rec.per_information29
  					,p_per_information30             => rec.per_information30
  					,p_date_of_death                 => rec.date_of_death
  					,p_background_check_status       => rec.background_check_status
  					,p_background_date_check         => rec.background_date_check
  					,p_blood_type                    => rec.blood_type
  					,p_correspondence_language       => rec.correspondence_language
  					,p_fast_path_employee            => rec.fast_path_employee
  					,p_fte_capacity                  => rec.fte_capacity
  					,p_honors                        => rec.honors
  					,p_internal_location             => rec.internal_location
  					,p_last_medical_test_by          => rec.last_medical_test_by
  					,p_last_medical_test_date        => rec.last_medical_test_date
  					,p_mailstop                      => rec.mailstop
  					,p_office_number                 => rec.office_number
  					,p_on_military_service           => rec.on_military_service
  					,p_pre_name_adjunct              => rec.pre_name_adjunct
  					,p_projected_start_date          => rec.projected_start_date 
  					,p_resume_exists                 => rec.resume_exists
  					,p_resume_last_updated           => rec.resume_last_updated
  					,p_second_passport_exists        => rec.second_passport_exists
  					,p_student_status                => rec.student_status
  					,p_work_schedule                 => rec.work_schedule 
  					,p_suffix                        => rec.suffix
  					,p_benefit_group_id              => rec.benefit_group_id
  					,p_receipt_of_death_cert_date    => rec.receipt_of_death_cert_date
  					,p_coord_ben_med_pln_no          => rec.coord_ben_med_pln_no
  					,p_coord_ben_no_cvg_flag         => NVL(rec.coord_ben_no_cvg_flag,'N')
  					,p_coord_ben_med_ext_er          => rec.coord_ben_med_ext_er
  					,p_coord_ben_med_pl_name         => rec.coord_ben_med_pl_name
  					,p_coord_ben_med_insr_crr_name   => rec.coord_ben_med_insr_crr_name
  					,p_coord_ben_med_insr_crr_ident  => rec.coord_ben_med_insr_crr_ident
  					,p_coord_ben_med_cvg_strt_dt     => rec.coord_ben_med_cvg_strt_dt
  					,p_coord_ben_med_cvg_end_dt      => rec.coord_ben_med_cvg_end_dt
  					,p_uses_tobacco_flag             => rec.uses_tobacco_flag 
  					,p_dpdnt_adoption_date           => rec.dpdnt_adoption_date
  					,p_dpdnt_vlntry_svce_flag        => NVL(rec.dpdnt_vlntry_svce_flag, 'N')
  					,p_original_date_of_hire         => rec.original_date_of_hire
  					,p_adjusted_svc_date             => NULL
  					,p_town_of_birth                 => rec.town_of_birth
  					,p_region_of_birth               => rec.region_of_birth
  					,p_country_of_birth              => rec.country_of_birth
  					,p_global_person_id              => rec.global_person_id
  					,p_party_id                      => rec.party_id
  					,p_person_id                     => l_new_person_id 
  					,p_assignment_id                 => l_new_assignment_id
  					,p_per_object_version_number     => l_new_per_obj_ver_num
  					,p_asg_object_version_number     => l_new_asg_obj_ver_num
  					,p_per_effective_start_date      => l_new_per_effective_start_date
  					,p_per_effective_end_date        => l_new_per_effective_end_date
  					,p_full_name                     => l_new_full_name
  					,p_per_comment_id                => l_new_per_comment_id
  					,p_assignment_sequence           => l_new_assignment_sequence
  					,p_assignment_number             => l_new_assignment_number
  					,p_name_combination_warning      => l_new_name_combination_warning
  					,p_assign_payroll_warning        => l_new_assign_payroll_warning
 				);*/
 				
		 
               hr_hk_employee_api.create_hk_employee(
                   p_hire_date                     => sysdate
                  ,p_business_group_id             => l_bg_id
                  ,p_last_name                     => rec.last_name
                  ,p_sex                           => nvl(rec.sex, 'M')
                  ,p_person_type_id                => l_person_type_id
                  ,p_email_address                 => rec.email_address
                  ,p_employee_number               => rec.employee_number
                  ,p_expense_check_send_to_addres  => 'O'
                  ,p_first_name                    => rec.first_name
                  ,p_middle_names                  => rec.middle_names
		  		  ,p_hkid_number		   		   =>'A123456(3)'
                  ,p_title                         => UPPER(rec.title)
		  		  ,p_work_telephone                => rec.work_telephone
                  ,p_attribute1                    => rec.per_information5
                  ,p_hk_full_name                  => rec.last_name || ', ' || rec.first_name || ' ' || rec.middle_names
                  ,p_person_id                     => l_new_person_id
                  ,p_assignment_id                 => l_new_assignment_id 
                  ,p_per_object_version_number     => l_new_per_obj_ver_num
                  ,p_asg_object_version_number     => l_new_asg_obj_ver_num 
                  ,p_per_effective_start_date      => l_new_per_effective_start_date
                  ,p_per_effective_end_date        => l_new_per_effective_end_date 
                  ,p_full_name                     => l_new_full_name
                  ,p_per_comment_id                => l_new_per_comment_id
                  ,p_assignment_sequence           => l_new_assignment_sequence
                  ,p_assignment_number             => l_new_assignment_number
                  ,p_name_combination_warning      => l_new_name_combination_warning
                  ,p_assign_payroll_warning        => l_new_assign_payroll_warning
                  ,p_orig_hire_warning             => l_new_orig_hire_warning
               );
	      				
 				
		 INSERT INTO crst_fnd_per_all_people_map (person_id, person_id_n) 
   										 VALUES  (rec.person_id, l_new_person_id);		
   										 
				 
		UPDATE per_all_people_f
		SET NATIONAL_IDENTIFIER = rec.national_identifier,
			COORD_BEN_NO_CVG_FLAG = rec.COORD_BEN_NO_CVG_FLAG,
			DPDNT_VLNTRY_SVCE_FLAG = rec.DPDNT_VLNTRY_SVCE_FLAG,
			PER_INFORMATION_CATEGORY = rec.PER_INFORMATION_CATEGORY,
			PER_INFORMATION6 = rec.per_information6,
			ORIGINAL_DATE_OF_HIRE = rec.ORIGINAL_DATE_OF_HIRE,
			start_date	=  rec.start_date,
			effective_start_date = rec.effective_start_date
		WHERE person_id = l_new_person_id
		AND EFFECTIVE_START_DATE = l_new_per_effective_start_date
   		and EFFECTIVE_END_DATE = l_new_per_effective_end_date 
		AND business_group_id = l_bg_id;	
		 		   										 		   			
		
   	END LOOP;
   	
   	COMMIT;
   	dbms_output.put_line('COMMIT complete.');
EXCEPTION
	WHEN func_error THEN
		dbms_output.put_line ('Error: '||'capbrimp_common.getBGID');
	
	WHEN OTHERS THEN		
		dbms_output.put_line ('Error: '||SQLERRM);
		ROLLBACK;
END;
/